import pymysql
import json


class MySQLHelper:

    def __init__(self, host='127.0.0.1', port=3306, user='root', password='123456', db='test'):
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.db = db

    def connect(self):
        self.conn = pymysql.connect(host=self.host, port=self.port, user=self.user, password=self.password, db=self.db)
        self.cursor = self.conn.cursor()

    def close(self):
        self.cursor.close()
        self.conn.close()

    def execute(self, sql, data=None):
        if data:
            self.cursor.execute(sql, data)
        else:
            self.cursor.execute(sql)
        self.conn.commit()

    # 查询所有数据，数据量过大时慎用
    def fetch_all(self, sql):
        self.cursor.execute(sql)
        return self.cursor.fetchall()

    # 查询一条数据，将结果转为字典
    def fetch_one2dict(self, sql):
        self.cursor.execute(sql)
        result = self.cursor.fetchone()
        my_list = list(result)

        for i in range(len(my_list)):
            if type(my_list[i]) is bytes:
                my_list[i] = my_list[i].decode('utf-8')
        result = tuple(my_list)
        # 将字段名和对应的值转为字典
        columns = [column[0] for column in self.cursor.description]
        data = dict(zip(columns, result))

        # 将字典对象转换为 JSON 字符串
        json_str = json.dumps(data)
        return json_str

    def select(self, table, columns, condition=None):
        column_str = ",".join(columns)
        sql = f"SELECT {column_str} FROM {table}"
        if condition:
            sql += f" WHERE {condition}"
        return self.fetch_all(sql)

    def insert(self, table, data):
        # {'id':101,'name':'gail','password':'123123'}
        column_str = ",".join(data.keys())
        value_str = ",".join(["'%s'" % v for v in data.values()])
        sql = f"INSERT INTO {table}({column_str}) VALUES({value_str})"
        self.execute(sql)

    def update(self, table, data, condition=None):
        update_str = ",".join([f"{k}='{v}'" for k, v in data.items()])
        sql = f"UPDATE {table} SET {update_str}"
        if condition:
            sql += f" WHERE {condition}"
        self.execute(sql)

    def delete(self, table, condition):
        sql = f"DELETE FROM {table} WHERE {condition}"
        self.execute(sql)

